﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using Ebiz.libs;
using System.Web.Services;
using System.Web.Script.Services;

namespace Ebiz
{
    public partial class ManageUser : System.Web.UI.Page
    {
        public string UserList = "";
        public string RoleList = "";
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack){
                UserList = LoadUsers();
                LoadRole();
            }else if (IsPostBack) {
                string UserName = txtUserName.Text;
                if (!(string.IsNullOrEmpty(UserName) || UserName == "0")) {
                    string IDNetwork = txtIDNet.Text;
                    string FullName = txtFName.Text;
                    string EmailAddr = txtEmail.Text;
                    string IDRole = cbRole.SelectedValue;
                    string ApvActor = cbApvAuth.SelectedValue;
                    string Mode = txtMode.Value.ToString();

                    string qry = "";
                    switch (Mode) { 
                        case "edit":
                            qry = "UPDATE UserAccounts SET IDNetwork = @IDNetwork, FullName = @Fullname, " +
                                "Email = @Email, IDRole = @IDRole, AppActor = @AppActor WHERE " +
                                "(UserName = @UserName)";
                            break;
                        case "add":
                            qry = "INSERT INTO UserAccounts (UserName, IDNetwork, FullName, Email, IDRole, " + 
                                "AppActor) VALUES (@UserName, @IDNetwork, @FullName, @Email, @IDRole, @AppActor)";
                            break;
                    }
                    SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
                    using (cn) {
                        try {
                            cn.Open();
                            SqlCommand cmd = new SqlCommand(qry, cn);
                            cmd.Parameters.AddWithValue("@IDNetwork", IDNetwork);
                            cmd.Parameters.AddWithValue("@FullName", FullName);
                            cmd.Parameters.AddWithValue("@Email", EmailAddr);
                            cmd.Parameters.AddWithValue("@IDRole", IDRole);
                            cmd.Parameters.AddWithValue("@AppActor", ApvActor);
                            cmd.Parameters.AddWithValue("@UserName", UserName);

                            if (cmd.ExecuteNonQuery() > 0){
                                Response.Write("<script>alert('Update data success')</script>");
                                Response.Redirect("ManageUser.aspx", true);
                            } else {
                                Response.Write("<script>alert('Update data failed')</script>");
                                Response.Redirect("ManageUser.aspx", true);
                            }
                        }catch(Exception ex){
                            string errMsg = ex.Message;
                        }finally{
                            if (cn.State == ConnectionState.Open) {
                                cn.Close();
                            }
                        }
                    }
                }else{
                    Response.Write("<script>alert('Update data failed')</script>");
                    Response.Redirect("ManageUser.aspx", true);
                }
            }
        }

        private static string LoadUsers(){
            string result = "";
            string strQuery = "SELECT UserAccounts.UserName AS Uname, UserAccounts.IDNetwork AS IDNetw, " +
                "UserAccounts.FullName AS FName, UserAccounts.IDRole AS RoleID, mstRole.RoleAlias AS Role " +
                "FROM UserAccounts INNER JOIN mstRole ON UserAccounts.IDRole = mstRole.IDRole " +
                "WHERE (UserAccounts.StatusCode = '101') AND (UserAccounts.IDRole <> 1) " +
                "ORDER BY FName";
            string strCon = DBUtilities.strConnection("DevCon");
            SqlConnection sqlCon = new SqlConnection(strCon);
            using (sqlCon){
                sqlCon.Open();
                SqlCommand sqlCmd = new SqlCommand(strQuery, sqlCon);
                SqlDataReader sqlReader = sqlCmd.ExecuteReader();
                if (sqlReader.HasRows){
                    string TRClass = "";
                    //string TRStart = "<tr " + TRClass + "><td>";
                    string TDStart = "<td>";
                    string TDStartLeft = "<td style='text-align:left;'>";
                    string TDEnd = "</td>";
                    string TREnd = "</td></tr>";
                    string Imgs = "<img src='images/info.png' class='btnView' title='Details' />";
                    string Rows = "";

                    int idx = 0;
                    while (sqlReader.Read()){
                        idx++;
                        string UserName = sqlReader.GetValue(0).ToString();
                        string IDNetwork = sqlReader.GetValue(1).ToString();
                        string FullName = sqlReader.GetValue(2).ToString();
                        string RoleAlias = sqlReader.GetValue(4).ToString();

                        TRClass = ((idx % 2) == 1) ? "Class='odd'" : "Class='even'";
                        string TRStart = "<tr " + TRClass + "><td style='text-align:left;'>";

                        string tmpRow = TRStart + UserName + TDEnd + TDStartLeft + IDNetwork + TDEnd +
                            TDStartLeft + FullName + TDEnd + TDStart + RoleAlias + TDEnd +
                            TDStart + Imgs + TREnd;

                        Rows += tmpRow;
                    }
                    result = Rows;
                }
            }
            return result;
        }

        private void LoadRole() {
            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT IDRole, RoleName FROM " + dbName + ".dbo.mstRole WHERE " +
                "(StatusCode = '101') AND (IDRole != 1)";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        while (rdr.Read()){
                            string sKey = rdr.GetValue(0).ToString();
                            string sVal = rdr.GetValue(1).ToString();

                            string opt = "<option value='" + sKey + "'>" +
                                sVal + "</option>\n";

                            cbRole.Items.Add(new ListItem(sVal, sKey));
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static List<object> ViewData(string UserName) {
            List<object> result = new List<object>();
            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT UserAccounts.Email, UserAccounts.AppActor, UserAccounts.IDRole, " +
                "mstRole.RoleName FROM " + dbName + ".dbo.UserAccounts INNER JOIN " + dbName + ".dbo.mstRole " +
                "ON mstRole.IDRole = UserAccounts.IDRole WHERE " +
                "(UserAccounts.UserName = @UserName)";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn) {
                try { 
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@UserName", UserName);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        if (rdr.Read()){
                            string Email = rdr.GetValue(0).ToString();
                            string AppActor = rdr.GetValue(1).ToString();
                            string IDRole = rdr.GetValue(2).ToString();
                            string RoleName = rdr.GetValue(3).ToString();

                            Dictionary<string, object> dct = new Dictionary<string, object>();
                            dct.Add("Email", Email);
                            dct.Add("AppActor", AppActor);
                            dct.Add("IDRole", IDRole);
                            dct.Add("RoleName", RoleName);

                            result.Add(1);
                            result.Add(dct);
                        }
                    }else {
                        result.Add(0);
                        result.Add("Data not found");
                    }
                }catch(Exception ex){
                    string errMsg = ex.Message;
                    result.Add(0);
                    result.Add(errMsg);
                }finally{
                    if(cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }

            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static List<object> DeleteUser(string User){
            List<object> result = new List<object>();
            if (string.IsNullOrEmpty(User)){
                result.Add(0);
                result.Add("Empty data");
            }else{
                string dbName = DBUtilities.GetDBName("devCon");
                string qry = "UPDATE " + dbName + ".dbo.UserAccounts SET UserAccounts.StatusCode = '104' " +
                    "WHERE (UserAccounts.UserName = @UserName)";
                SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
                using (cn) {
                    try {
                        cn.Open();
                        SqlCommand cmd = new SqlCommand(qry, cn);
                        cmd.Parameters.AddWithValue("@UserName", User);
                        int tmpResult = cmd.ExecuteNonQuery();
                        if (tmpResult > 0){
                            result.Add(1);
                            result.Add("Success");
                        }else{
                            result.Add(0);
                            result.Add("Failed");
                        }
                    }catch (Exception ex) {
                        string errMsg = ex.Message;
                        result.Add(0);
                        result.Add(errMsg);
                    }finally {
                        if (cn.State == ConnectionState.Open) {
                            cn.Close();
                        }
                    }
                }
            }
            return result;
        }
    }
}